//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Microsoft.Research.DataLayer
{
    public partial class SQLConnectionWorker
    {
        
        /// <summary>
        /// Execute the detailed search based on the clause created by the client
        /// </summary>
        /// <param name="clause"></param>
        /// <param name="results"></param>
        void ExecuteSearch(Connection.Storage objType, SearchQuery query, ObjectCreator creator, out List<IObject> results)
        {
            string sqlQuery = SearchBuildQuery(objType, query);

            SqlCommand cmd = new SqlCommand(sqlQuery, conn);
            cmd.CommandType = System.Data.CommandType.Text;

            List<Parameter> nullParamList = new List<Parameter>();
            ReadManyInternal(cmd, Action.Search, ref nullParamList, creator, out results);
        }

        /// <summary>
        /// Execute the detailed search based on the clause created by the client
        /// </summary>
        /// <param name="clause"></param>
        /// <param name="results"></param>
        void ExecuteSearch(Connection.Storage objType, string condition, ObjectCreator creator, out List<IObject> results)
        {
            string queryStmt = string.Empty; 

            string queryJoins = string.Empty;
            //Create search context
            SQLSearchProviderContext context = new SQLSearchProviderContext(objType);
            
            //Build the relationship clause
            foreach (string joinTable in context.JoinList)
            {
                queryJoins += joinTable;
            }
            queryStmt = string.Format("SELECT * FROM {0}_Base {1} WHERE {2}", objType.Name, queryJoins, condition);



            SqlCommand cmd = new SqlCommand(queryStmt, conn);
            cmd.CommandType = System.Data.CommandType.Text;

            List<Parameter> nullParamList = new List<Parameter>();
            ReadManyInternal(cmd, Action.Search, ref nullParamList, creator, out results);
        }

        private string SearchBuildQuery(Connection.Storage objType, SearchQuery query)
        {
            string queryStmt = null;
            string queryCond = null;
            string queryJoins = null;
            //Create search context
            SQLSearchProviderContext context = new SQLSearchProviderContext(objType);
            query.ProviderContext = context;
            
            queryCond = SearchBuildClause(query, query.GetQuery());
            //Build the relationship clause
            foreach (string joinTable in context.JoinList)
            {
                queryJoins += joinTable;
            }
            queryStmt = string.Format("SELECT * FROM {0}_Base {1} WHERE {2}", objType.Name, queryJoins, queryCond);

            return queryStmt;
        }

        #region Search Build Helper
        private static class SearchBuildHelper
        {
            public delegate string ProcessClause(SearchQuery query, ISearchClauseImpl clause, BaseField field);

            private static Dictionary<System.Type, SearchBuildHelper.ProcessClause> fieldTypeMap;

            public static Dictionary<System.Type, SearchBuildHelper.ProcessClause> FieldProcessors
            {
                get { return fieldTypeMap; }
            }

            static SearchBuildHelper()
            {
                fieldTypeMap = new Dictionary<System.Type, SearchBuildHelper.ProcessClause>();
                fieldTypeMap.Add(typeof(StringField), ProcessStringClause);
                fieldTypeMap.Add(typeof(DateTimeField), ProcessDateTimeClause);
                fieldTypeMap.Add(typeof(IntegerField), ProcessIntegerClause);
                fieldTypeMap.Add(typeof(EnumFieldBase), ProcessEnumClause); 
                fieldTypeMap.Add(typeof(URIField), ProcessURIClause);
                fieldTypeMap.Add(typeof(ObjectFieldBase), ProcessObjectClause);
                fieldTypeMap.Add(typeof(BooleanField), ProcessBooleanClause);
                fieldTypeMap.Add(typeof(RelationSimpleFieldBase), ProcessSimpleRelationClause);
                fieldTypeMap.Add(typeof(RelationObjectFieldBase), ProcessObjectRelationClause); 
            }

            private static string ProcessStringClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
            {
                StringField.Condition fCond = (StringField.Condition)clauseImpl.Op;
                string op;
                string wild = "";

                switch (fCond)
                {
                    case StringField.Condition.Equals:
                        op = "=";
                        break;
                    case StringField.Condition.NotEquals:
                        op = "<>";
                        break;
                    case StringField.Condition.Contains:
                        op = "LIKE";
                        wild = "%";
                        break;
                    case StringField.Condition.NotContains:
                        op = "NOT LIKE";
                        wild = "%";
                        break;
                    default:
                        throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
                }

                string pred = string.Format("[{0}] {1} '{3}{2}{3}'", clauseImpl.Name, op, SqlEscapeString((string)clauseImpl.Value), wild);

                return pred;
            }

            private static string ProcessDateTimeClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
            {
                DateTimeField.Condition fCond = (DateTimeField.Condition)clauseImpl.Op;
                string op;

                switch (fCond)
                {
                    case DateTimeField.Condition.Equals:
                        op = "=";
                        break;
                    case DateTimeField.Condition.NotEquals:
                        op = "<>";
                        break;
                    case DateTimeField.Condition.After:
                        op = ">";
                        break;
                    case DateTimeField.Condition.AfterOrEqual:
                        op = ">=";
                        break;
                    case DateTimeField.Condition.Before:
                        op = "<";
                        break;
                    case DateTimeField.Condition.BeforeOrEqual:
                        op = "<=";
                        break;
                    default:
                        throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
                }

                string pred = string.Format("[{0}] {1} CAST('{2}.{3}' as datetime)", clauseImpl.Name, op, ((DateTime)clauseImpl.Value).ToString("s"),
                    ((DateTime)clauseImpl.Value).Millisecond);

                return pred;
            }

            private static string ProcessIntegerClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
            {
                IntegerField.Condition fCond = (IntegerField.Condition)clauseImpl.Op;
                string op;

                switch (fCond)
                {
                    case IntegerField.Condition.Equals:
                        op = "=";
                        break;
                    case IntegerField.Condition.NotEquals:
                        op = "<>";
                        break;
                    case IntegerField.Condition.GreaterThan:
                        op = ">";
                        break;
                    case IntegerField.Condition.GreaterThanOrEqual:
                        op = ">=";
                        break;
                    case IntegerField.Condition.LessThan:
                        op = "<";
                        break;
                    case IntegerField.Condition.LessThanOrEqual:
                        op = "<=";
                        break;
                    default:
                        throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
                }

                string pred = string.Format("[{0}] {1} {2}", clauseImpl.Name, op, (int)clauseImpl.Value);

                return pred;
            }

            private static string ProcessEnumClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
            {
                EnumFieldBase.Condition fCond = (EnumFieldBase.Condition)clauseImpl.Op;

                string op;

                switch (fCond)
                {
                    case EnumFieldBase.Condition.Equals:
                        op = "=";
                        break;
                    case EnumFieldBase.Condition.NotEquals:
                        op = "<>";
                        break;
                    default:
                        throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
                }

                string pred = string.Format("[{0}] {1} '{2}'", clauseImpl.Name, op, clauseImpl.Value);

                return pred;
            }

            private static string ProcessURIClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
            {
                URIField.Condition fCond = (URIField.Condition)clauseImpl.Op;
                string op;
                string wildS = "";
                string wildE = "";

                switch (fCond)
                {
                    case URIField.Condition.Equals:
                        op = "=";
                        break;
                    case URIField.Condition.NotEquals:
                        op = "<>";
                        break;
                    case URIField.Condition.Contains:
                        op = "LIKE";
                        wildS = wildE = "%";
                        break;
                    case URIField.Condition.NotContains:
                        op = "NOT LIKE";
                        wildS = wildE = "%";
                        break;
                    case URIField.Condition.UseProtocol:
                        op = "LIKE";
                        wildE = "://%";
                        break;
                    case URIField.Condition.NotUseProtocol:
                        op = "NOT LIKE";
                        wildE = "://%";
                        break;
                    default:
                        throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
                }

                string pred = string.Format("[{0}] {1} '{3}{2}{4}'", clauseImpl.Name, op, SqlEscapeString((string)clauseImpl.Value), wildS, wildE);

                return pred;
            }

            private static string ProcessObjectClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
            {
                ObjectFieldBase.Condition fCond = (ObjectFieldBase.Condition)clauseImpl.Op;

                string op;
                int style;

                switch (fCond)
                {
                    case ObjectFieldBase.Condition.Equals:
                        op = "=";
                        style = 1;
                        break;
                    case ObjectFieldBase.Condition.NotEquals:
                        op = "<>";
                        style = 1;
                        break;
                    case ObjectFieldBase.Condition.IsNull:
                        op = "";
                        style = 2;
                        break;
                    case ObjectFieldBase.Condition.IsNotNull:
                        op = "NOT";
                        style = 2;
                        break;
                    default:
                        throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
                }

                string pred;
                
                if( style == 1 )
                    pred = string.Format("[{0}ID] {1} '{2}'", clauseImpl.Name, op, ((IObject)clauseImpl.Value).ID);
                else
                    pred = string.Format("[{0}ID] IS {1} NULL", clauseImpl.Name, op);


                return pred;
            }


            private static string ProcessBooleanClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
            {
                BooleanField.Condition fCond = (BooleanField.Condition)clauseImpl.Op;

                string op;

                switch (fCond)
                {
                    case BooleanField.Condition.Equals:
                        op = "=";
                        break;
                    case BooleanField.Condition.NotEquals:
                        op = "<>";
                        break;
                    default:
                        throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
                }

                string pred = string.Format("[{0}] {1} '{2}'", clauseImpl.Name, op, clauseImpl.Value);

                return pred;
            }
            private static string SqlEscapeString(string str)
            {
                string[] badChars = new string[] { "'" };//, "\\" };
                string[] goodChars = new string[] { "''" };//, "\\\\" };

                System.Diagnostics.Debug.Assert(badChars.Length == goodChars.Length);

                for (int i = 0; i < badChars.Length; i++)
                {
                    str = str.Replace(badChars[i], goodChars[i]);
                }

                return str;
            }
        }


        private static string ProcessSimpleRelationClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
        {
            RelationFieldBase.Condition fCond = (RelationFieldBase.Condition)clauseImpl.Op;

            RelationSimpleFieldBase simpleRelation = field as RelationSimpleFieldBase;
            
            string op;
            int style;

            switch (fCond)
            {
                case RelationFieldBase.Condition.Equals:
                    op = "=";
                    style = 1;
                    break;
                case RelationFieldBase.Condition.NotEquals:
                    op = "<>";
                    style = 1;
                    break;
                case RelationFieldBase.Condition.IsNull:
                    op = "";
                    style = 2;
                    break;
                case RelationFieldBase.Condition.IsNotNull:
                    op = "NOT";
                    style = 2;
                    break;
                default:
                    throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
            }

            string pred;

            if (style == 1)
            {
                if (null != query.ProviderContext)
                {
                    SQLSearchProviderContext contxt = (SQLSearchProviderContext)query.ProviderContext;
                    string queryJoins = null;
                    string table = ObjectKind(contxt.Object, ((IObject)clauseImpl.Value).ObjType);
                    //Simple relation will have normal table and field names
                    if (simpleRelation.Relationship == RelationFieldBase.RelationshipType.Simple)
                    {
                        queryJoins = string.Format(" INNER JOIN {0} {0}{1} on {0}{1}.{1}ID = ID", table, contxt.Object.Name);
                    }
                    //self relation
                    else if (simpleRelation.Relationship == RelationFieldBase.RelationshipType.Parent)
                    {
                        queryJoins = string.Format(" INNER JOIN {0} on {0}_2ID = ID", contxt.Object.Name);
                    }
                    else
                    {
                        queryJoins = string.Format(" INNER JOIN {0} on {0}_1ID = ID", contxt.Object.Name);
                    }

                    if (!contxt.JoinList.Contains(queryJoins))
                        contxt.JoinList.Add(queryJoins);
                }
                
                pred = string.Format("[{0}ID] {1} '{2}'", clauseImpl.Name, op, ((IObject)clauseImpl.Value).ID);
                   
            }
            else
                pred = string.Format("[{0}ID] IS {1} NULL", clauseImpl.Name, op);



            return pred;
        }


        private static string ProcessObjectRelationClause(SearchQuery query, ISearchClauseImpl clauseImpl, BaseField field)
        {
            RelationFieldBase.Condition fCond = (RelationFieldBase.Condition)clauseImpl.Op;

            SearchClause cls = clauseImpl as SearchClause;
            //SearchClauseWrapper wrapper = cls as SearchClauseWrapper;
            RelationObjectFieldBase objectRelation = field as RelationObjectFieldBase;
            
            string op;
            int style;

            switch (fCond)
            {
                case RelationFieldBase.Condition.Equals:
                    op = "=";
                    style = 1;
                    break;
                case RelationFieldBase.Condition.NotEquals:
                    op = "<>";
                    style = 1;
                    break;
                case RelationFieldBase.Condition.IsNull:
                    op = "";
                    style = 2;
                    break;
                case RelationFieldBase.Condition.IsNotNull:
                    op = "NOT";
                    style = 2;
                    break;
                default:
                    throw new NotImplementedException(string.Format("SQL comparison '{0}' not implemented", fCond));
            }

            string pred = null;

            if (style == 1)
            {
                if (null != query.ProviderContext)
                {
                    SQLSearchProviderContext contxt = (SQLSearchProviderContext)query.ProviderContext;
                    string queryJoins = null;
                    //Simple relation will have normal table and field names
                    if (objectRelation.Relationship == RelationFieldBase.RelationshipType.Simple)
                    {
                        queryJoins = string.Format(" INNER JOIN {0} {0}{1} on {0}{1}.{1}ID = ID", objectRelation.TableName, contxt.Object.Name);
                    }
                    //self relation parent
                    else if (objectRelation.Relationship == RelationFieldBase.RelationshipType.Parent)
                    {
                        queryJoins = string.Format(" INNER JOIN {0} {0}{1} on {0}{1}.{1}_1ID = ID", objectRelation.TableName, contxt.Object.Name);
                    }
                    //self relation child
                    else
                    {
                        queryJoins = string.Format(" INNER JOIN {0} on {0}{1}.{0}_2ID = ID", contxt.Object.Name);
                    }

                    if (!contxt.JoinList.Contains(queryJoins))
                        contxt.JoinList.Add(queryJoins);
                }
                pred = string.Format("[ID] {1} '{2}'", clauseImpl.Name, op, ((IObject)clauseImpl.Value).ID);
            }
            else
                pred = string.Format("[ID] IS {1} NULL", clauseImpl.Name, op);



            return pred;
        }


        #endregion

        private string SearchBuildClause(SearchQuery query, ISearchClause clause)
        {
            // Sanity check. If happens it is a code bug.
            System.Diagnostics.Debug.Assert(clause is SearchClauseWrapper || clause is ISearchGroup);
            if (!(clause is SearchClauseWrapper) && !(clause is ISearchGroup))
            {
                throw new ArgumentException("clause is not a SearchClauseWrapper or ISearchGroup");
            }

            string pred = null;

            if (clause is ISearchGroup)
            {
                //
                // Search group
                //
                pred = SearchBuildGroup(query, clause as ISearchGroup);
            }
            else
            {
                //
                // Simple expression
                //
                SearchClauseWrapper clauseWrapper = clause as SearchClauseWrapper;
                
                ISearchClauseImpl clauseImpl = clauseWrapper.ClauseImpl;

                // Find the appropriate handler
                System.Type handler = null;
                foreach (System.Type baseType in SearchBuildHelper.FieldProcessors.Keys)
                { 
                    if( clauseWrapper.Field.GetType().IsSubclassOf(baseType) )
                    {
                        handler = baseType;
                        break;
                    }
                }

                System.Diagnostics.Debug.Assert(handler != null);

                if( handler == null )
                    throw new NotImplementedException(string.Format("Unknown field type '{0}' for query", clauseWrapper.Field.GetType()));

                // Safe to call the processor directly
                pred = SearchBuildHelper.FieldProcessors[handler](query,clauseImpl, clauseWrapper.Field);
            }

            return pred;
        }

        private string SearchBuildGroup(SearchQuery query, ISearchGroup inGroup)
        {
            string queryCond;

            if (inGroup is ISearchGroupBinary)
            {
                // Composite clause
                ISearchGroupBinary group = inGroup as ISearchGroupBinary;
                string p1Query = SearchBuildClause(query, group.P1);
                string p2Query = SearchBuildClause(query, group.P2);

                switch (group.OP)
                {
                    case SearchRelBinary.And:
                        queryCond = string.Format("(({0}) AND ({1}))", p1Query, p2Query);
                        break;
                    case SearchRelBinary.Or:
                        queryCond = string.Format("(({0}) OR ({1}))", p1Query, p2Query);
                        break;
                    default:
                        throw new NotImplementedException();
                }
            }
            else if (inGroup is ISearchGroupUnary)
            {
                ISearchGroupUnary group = inGroup as ISearchGroupUnary;
                string p1Query = SearchBuildClause(query, group.P1);

                switch (group.OP)
                {
                    case SearchRelUnary.Not:
                        queryCond = string.Format("(NOT ({1}))", null, p1Query);
                        break;
                    default:
                        throw new NotImplementedException();
                }
            }
            else
            {
                throw new NotImplementedException();
            }

            return queryCond;
        }
    }
}
